﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace EventManager
{
    public class connectdata
    {
        public string connectionString = @"Data Source=(local);Initial Catalog=EventDb;Integrated Security=True";
        public void setConnectionString(string s)
        {
            connectionString = s;
        }
        public DataTable connect(string sql)
        {
            string cString = connectionString;
            SqlConnection database;
            try
            {

                database = new SqlConnection(cString);
                database.Open();
                SqlCommand SQLQuery = new SqlCommand();
                DataTable data = null;
                SQLQuery.Connection = null;
                SqlDataAdapter dataAdapter = null;
                SQLQuery.CommandText = sql;
                SQLQuery.Connection = database;
                data = new DataTable();
                dataAdapter = new SqlDataAdapter(SQLQuery);
                dataAdapter.Fill(data);
                return data;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }

        }
        public void query(string sql)
        {
            string cString = connectionString;
            SqlConnection database;
            try
            {
                database = new SqlConnection(cString);
                database.Open();
                SqlCommand sqlquery = new SqlCommand();
                sqlquery.CommandText = sql;
                sqlquery.Connection = database;
                sqlquery.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }
        public connectdata()
        {}
        public connectdata(string connectionString)
        {
            this.connectionString = connectionString;
        }
    }
    public class Event
    {
        public int EvID { get; set; }
        public string evtitle { get; set; }
        public string venuename { get; set; }
        public string addr { get; set; }
        public DateTime b { get; set; }
        public DateTime e { get; set; }
        public int uid { get; set; }
        public int lid { get; set; }
        public string evdes { get; set; }
        public string org { get; set; }
        public struct Location
        {
            public string city { get; set; }
            public string province { get; set; }
            public string country { get; set; }
        }
        public Location location;
        public Event()
        {
        }
        public Event(int id, string title, string venue, string addr, DateTime b, DateTime e, int uid, int lid, string evdes, string org, string city, string province, string country)
        {
            this.EvID = id;
            this.evtitle = title;
            this.venuename = venue;
            this.addr = addr;
            this.b = b;
            this.e = e;
            this.uid = uid;
            this.lid = lid;
            this.evdes = evdes;
            this.org = org;
            location.city = city;
            location.province = province;
            location.country = country;
        }
    }
    public class Ticket
    {
        public int TID;
        public string TickName;
        public string TickType;
        public int TickNum;
        public float TickPrice;
        public float TickFee;
        public string TickStt;
        public int MaxOrder;
        public int EvID;
        public Ticket()
        {
            TID = -1;
            TickName = null;
            TickType = null;
            TickNum = -1;
            TickPrice = -1;
            TickFee = -1;
            TickStt = null;
            MaxOrder = -1;
        }
        public Ticket(int tid, string tickname, string ticktype, int ticknum, float tickprice, float tickfee, string tickstt, int maxorder)
        {
            this.TID = tid;
            this.TickName = tickname;
            this.TickNum = ticknum;
            this.TickPrice = tickprice;
            this.TickFee = tickfee;
            this.TickStt = tickstt;
            this.MaxOrder = maxorder;

        }
    }
    public class TicketInfo
    {
        public int TID;
        public string fname;
        public string lname;
        public string mobile;
        public string email;
        public string address;
        public string wplace;
        public DateTime dob;
        public TicketInfo(string fname, string lname, string mob, string email, string add, string wp, DateTime d)
        {
            this.fname = fname;
            this.lname = lname;
            this.mobile = mob;
            this.email = email;
            this.address = add;
            this.wplace = wp;
            this.dob = d;
        }
    }
    public class EventMgr
    {//Test province
        //Tested
        public bool CreateEv(string evtitle, string venuename, string addr, DateTime b, DateTime e, int uid, int lid, string evdes, string org)
        {
            connectdata a = new connectdata();
            DataTable d = a.connect("SELECT EvID FROM Event");
            int evid = d.Rows.Count;
            string crquery =
                "INSERT INTO Event([EvID],[EvTitle],[VenueName],[VenueAddr],[DateBegin],[DateEnd],[UserID],[LogoID],[EvDesc],[Orgz]) VALUES('" + evid.ToString() + "','" + evtitle + "','" + venuename + "','" + addr + "','" + b.ToShortDateString() + "','" + e.ToShortDateString() + "','" + uid.ToString() + "','" + lid.ToString() + "','" + evdes + "','" + org + "')";
            a.query(crquery);
            return true;
        }
        //Tested
        public bool CreateEv(Event x)
        {
            connectdata a = new connectdata();
            DataTable d = a.connect("SELECT EvID FROM Event");
            int evid = d.Rows.Count;
            string crquery =
                "INSERT INTO Event([EvID],[EvTitle],[VenueName],[VenueAddr],[DateBegin],[DateEnd],[UserID],[LogoID],[EvDesc],[Orgz],[City],[Province],[Country]) VALUES('" + evid.ToString() + "','" + x.evtitle + "','" + x.venuename + "','" + x.addr + "','" + x.b.ToShortDateString() + "','" + x.e.ToShortDateString() + "','" + x.uid.ToString() + "','" + x.lid.ToString() + "','" + x.evdes + "','" + x.org + "','" + x.location.city + "','" + x.location.province + "','" + x.location.country + "')";
            a.query(crquery);
            return true;
        }
        //TESTED
        public bool AddTicketToEv(int EvID, string TickName, string TickType, int TickNum, float TickPrice, float TickFee, string TickStt, int MaxOrder)
        {
            connectdata a = new connectdata();
            DataTable d = a.connect("SELECT TID FROM Ticket");
            int tid = d.Rows.Count;
            string query = " INSERT INTO Ticket(TID,TicketName,TickType,TickNum,TickPrice,TickFee,TickStt,EvID,MaxOrder) VALUES('" + (tid + 1).ToString() + "','" + TickName + "','" + TickType + "','" + TickNum.ToString() + "','" + TickPrice.ToString() + "','" + TickFee.ToString() + "','" + TickStt + "','" + EvID.ToString() + "','" + MaxOrder.ToString() + "')";
            a.query(query);
            return true;
        }
        public void RemoveTicket(int TID)
        {
            connectdata a = new connectdata();
            string query = "DELETE Ticket FROM Ticket WHERE TID=" + TID.ToString();
            a.query(query);
        }
        public bool UpdateTicket(int TID, Ticket t)
        {
            connectdata a = new connectdata();
            string query = "UPDATE Ticket SET ";
            if (t.TickName != null) query = query + " TicketName = '" + t.TickName + "', ";
            if (t.TickType != null) query = query + " TickType = '" + t.TickType + "', ";
            if (t.TickNum >= 0) query = query + " TickNum = '" + t.TickNum.ToString() + "', ";
            if (t.TickPrice >= 0) query = query + " TickPrice = '" + t.TickPrice.ToString() + "', ";
            if (t.TickFee >= 0) query = query + " TickFee = '" + t.TickFee.ToString() + "', ";
            if (t.TickStt != null) query = query + " TickStt = '" + t.TickStt + "', ";
            if (t.MaxOrder >= 0) query = query + " MaxOrder = '" + t.MaxOrder.ToString() + "'";
            a.query(query);
            return true;
        }
        public void UpdateEvent(int EvID, Event e)
        {
            connectdata a = new connectdata();
            string query = "UPDATE Event SET ";
            if (e.evtitle != null) query = query + " EvTitle = '" + e.evtitle + "', ";
            if (e.venuename != null) query = query + " VenueName = '" + e.venuename + "', ";
            if (e.addr != null) query = query + " VenueAddr = '" + e.addr + "', ";
            if (e.b != null) query = query + " DateBegin = '" + e.b.ToShortDateString() + "', ";
            if (e.e != null) query = query + " DateBegin = '" + e.e.ToShortDateString() + "', ";
            if (e.lid != null) query = query + " LogoID = '" + e.lid.ToString() + "', ";
            if (e.evdes != null) query = query + " EvDesc = '" + e.evdes + "', ";
            if (e.org != null) query = query + " Orgz = '" + e.org + "', ";
            if (e.location.city != null) query = query + " City = '" + e.location.city + "', ";
            if (e.location.province != null) query = query + " Province = '" + e.location.province + "', ";
            if (e.location.country != null) query = query + " Country = '" + e.location.country + "', ";
            a.query(query);

        }
        //TEsted
        public void OrderTickets(Ticket t, string fname, string lname, string email, int num)
        {
            connectdata a = new connectdata();
            DataTable d = a.connect("SELECT ID FROM [EventDb].[dbo].[OrderTicket]");
            string query;
            int id = d.Rows.Count;
            if (num > t.MaxOrder)
            {
                MessageBox.Show("Order quantity's over max limit order.");
            }
            else if (num > t.TickNum)
            {
                MessageBox.Show("Not enough ticket.");
            }
            else
            {
                query = "INSERT INTO [EventDb].[dbo].[OrderTicket](ID,TID,Quantity,Firstname,Lastname,Email) VALUES('" + (id + 1).ToString() + "','" + t.TID.ToString() + "','" + num.ToString() + "','" + fname + "','" + lname + "','" + email + "')";
                a.query(query);
                if (t.TickNum - num == 0)
                    query = "UPDATE Ticket SET [TickNum]='0', [TickStt]='Out of Stock' WHERE TID='" + t.TID + "'";
                else
                    query = "UPDATE Ticket SET TickNum='" + (t.TickNum - num).ToString() + "' WHERE TID='" + t.TID + "'";
                a.query(query);
            }

        }
        //Tested
        public void UpdateTicketInfo(int tid, TicketInfo e)
        {
            long temp = 1213;
            long.TryParse(e.mobile, out temp);
            if (temp != 0)
            {
                connectdata a = new connectdata();
                DataTable d = a.connect("SELECT ID FROM TicketInfo");
                string query = "INSERT INTO TicketInfo(ID,FirstName,LastName,Mobile,Email,Address,WorkAt,DateOfBirth) VALUES('" + tid + "','" + e.fname + "','" + e.lname + "','" + e.mobile + "','" + e.email + "','" + e.address + "','" + e.wplace + "','" + e.dob.ToShortDateString() + "')";
                for (int i = 0; i <= d.Rows.Count - 1; i++)
                {
                    if (d.Rows[i][0].ToString() == tid.ToString())
                    {
                        query = "UPDATE TicketInfo SET";
                        if (e.fname != null) query = query + " FirstName = '" + e.fname + "',";
                        if (e.lname != null) query = query + " LastName = '" + e.lname + "',";
                        if (e.mobile != null) query = query + " Mobile = '" + e.mobile + "' ,";
                        if (e.email != null) query = query + " Email = '" + e.email + "' ,";
                        if (e.address != null) query = query + " Address = '" + e.address + "' ,";
                        if (e.wplace != null) query = query + " WorkAt = '" + e.wplace + "' ,";
                        if (e.dob != null) query = query + " DateOfBirth = '" + e.dob.ToShortDateString() + "' ";
                        break;
                    }

                }
                a.query(query);
            }
            else MessageBox.Show("Not valid mobile number");
        }
        //=================================
        //Tested
        public List<Event> LoadEventByCity(string city)
        {
            List<Event> result = new List<Event>();
            connectdata a = new connectdata();
            string query = "SELECT * FROM Event WHERE Event.City='" + city + "'";
            DataTable d = a.connect(query);
            for (int i = 0; i <= d.Rows.Count - 1; i++)
            {
                Event e = new Event();
                e.EvID = Convert.ToInt32(d.Rows[i][0].ToString());
                e.evtitle = d.Rows[i][1].ToString();
                e.venuename = d.Rows[i][2].ToString();
                e.addr = d.Rows[i][3].ToString();
                e.b = Convert.ToDateTime(d.Rows[i][4].ToString());
                e.e = Convert.ToDateTime(d.Rows[i][5].ToString());
                e.uid = Convert.ToInt32(d.Rows[i][6].ToString());
                e.lid = Convert.ToInt32(d.Rows[i][7].ToString());
                e.evdes = d.Rows[i][8].ToString();
                e.org = d.Rows[i][9].ToString();
                e.location.city = d.Rows[i][10].ToString();
                e.location.province = d.Rows[i][11].ToString();
                e.location.country = d.Rows[i][12].ToString();
                result.Add(e);

            }
            return result;
        }
        public List<Event> LoadEventByProvince(string prov)
        {
            List<Event> result = new List<Event>();
            connectdata a = new connectdata();
            string query = "SELECT * FROM Event WHERE Event.Province='" + prov + "'";
            DataTable d = a.connect(query);
            for (int i = 0; i <= d.Rows.Count - 1; i++)
            {
                Event e = new Event();
                e.EvID = Convert.ToInt32(d.Rows[i][0].ToString());
                e.evtitle = d.Rows[i][1].ToString();
                e.venuename = d.Rows[i][2].ToString();
                e.addr = d.Rows[i][3].ToString();
                e.b = Convert.ToDateTime(d.Rows[i][4].ToString());
                e.e = Convert.ToDateTime(d.Rows[i][5].ToString());
                e.uid = Convert.ToInt32(d.Rows[i][6].ToString());
                e.lid = Convert.ToInt32(d.Rows[i][7].ToString());
                e.evdes = d.Rows[i][8].ToString();
                e.org = d.Rows[i][9].ToString();
                e.location.city = d.Rows[i][10].ToString();
                e.location.province = d.Rows[i][11].ToString();
                e.location.country = d.Rows[i][12].ToString();
                result.Add(e);

            }
            return result;
        }
        public List<Event> LoadEventByCountry(string country)
        {
            List<Event> result = new List<Event>();
            connectdata a = new connectdata();
            string query = "SELECT * FROM Event WHERE Event.Country='" + country + "'";
            DataTable d = a.connect(query);
            for (int i = 0; i <= d.Rows.Count - 1; i++)
            {
                Event e = new Event();
                e.EvID = Convert.ToInt32(d.Rows[i][0].ToString());
                e.evtitle = d.Rows[i][1].ToString();
                e.venuename = d.Rows[i][2].ToString();
                e.addr = d.Rows[i][3].ToString();
                e.b = Convert.ToDateTime(d.Rows[i][4].ToString());
                e.e = Convert.ToDateTime(d.Rows[i][5].ToString());
                e.uid = Convert.ToInt32(d.Rows[i][6].ToString());
                e.lid = Convert.ToInt32(d.Rows[i][7].ToString());
                e.evdes = d.Rows[i][8].ToString();
                e.org = d.Rows[i][9].ToString();
                e.location.city = d.Rows[i][10].ToString();
                e.location.province = d.Rows[i][11].ToString();
                e.location.country = d.Rows[i][12].ToString();
                result.Add(e);

            }
            return result;
        }
        public List<Event> LoadEventNearby(DateTime date)
        {
            List<Event> result = new List<Event>();
            connectdata a = new connectdata();
            string query = "SELECT * FROM Event ";
            DataTable d = a.connect(query);
            for (int i = 0; i <= d.Rows.Count - 1; i++)
            {
                Event e = new Event();
                e.b = Convert.ToDateTime(d.Rows[i][4].ToString());
                e.EvID = Convert.ToInt32(d.Rows[i][0].ToString());
                e.evtitle = d.Rows[i][1].ToString();
                e.venuename = d.Rows[i][2].ToString();
                e.addr = d.Rows[i][3].ToString();
                e.e = Convert.ToDateTime(d.Rows[i][5].ToString());
                e.uid = Convert.ToInt32(d.Rows[i][6].ToString());
                e.lid = Convert.ToInt32(d.Rows[i][7].ToString());
                e.evdes = d.Rows[i][8].ToString();
                e.org = d.Rows[i][9].ToString();
                e.location.city = d.Rows[i][10].ToString();
                e.location.province = d.Rows[i][11].ToString();
                e.location.country = d.Rows[i][12].ToString();
                if (((e.b.Month <= date.Month + 1) && (e.b.Month >= date.Month) && (e.b.Year == date.Year)) || ((date.Month + 1 > 12) && (e.b.Month <= 1) && (e.b.Year == date.Year + 1)))
                    result.Add(e);

            }
            return result;
        }
        public List<Ticket> LoadTicketListByEv(int id)
        {
            List<Ticket> result = new List<Ticket>();
            connectdata a = new connectdata();
            string query = "SELECT * FROM Ticket WHERE Ticket.EvID=" + id.ToString();
            DataTable d = a.connect(query);
            for (int i = 0; i <= d.Rows.Count - 1; i++)
            {
                Ticket temp = new Ticket();
                temp.TID = Convert.ToInt32(d.Rows[i][0].ToString());
                temp.TickName = d.Rows[i][1].ToString();
                temp.TickType = d.Rows[i][2].ToString();
                temp.TickNum = Convert.ToInt32(d.Rows[i][3].ToString());
                temp.TickPrice = (float)Convert.ToDouble(d.Rows[i][4].ToString());
                temp.TickFee = (float)Convert.ToDouble(d.Rows[i][5].ToString());
                temp.TickStt = d.Rows[i][6].ToString();
                temp.MaxOrder = Convert.ToInt32(d.Rows[i][8].ToString());
                result.Add(temp);
            }
            return result;
        }
        //Tested
        public string LoadUsernameByID(int uid)
        {
            connectdata a = new connectdata();
            DataTable d = a.connect("SELECT Username FROM User WHERE ID='" + uid.ToString() + "'");
            if (d.Rows.Count > 0) return d.Rows[0][0].ToString();
            else return null;
        }
        public int LoadUserIDByName(string username)
        {
            connectdata a = new connectdata();
            DataTable d = a.connect("SELECT ID FROM User WHERE Username='" + username + "'");
            if (d.Rows.Count > 0) return Convert.ToInt16(d.Rows[0][0].ToString());
            else return -1;
        }
        public Ticket LoadTicketByID(int id)
        {
            connectdata a = new connectdata();
            DataTable d = a.connect("SELECT * FROM Ticket WHERE TID='" + id.ToString() + "'");
            Ticket t = new Ticket();
            t.TID = Convert.ToInt32(d.Rows[0][0].ToString());
            t.TickName = d.Rows[0][1].ToString();
            t.TickType = d.Rows[0][2].ToString();
            t.TickNum = Convert.ToInt32(d.Rows[0][3].ToString());
            t.TickPrice = (float)Convert.ToDouble(d.Rows[0][4].ToString());
            t.TickFee = (float)Convert.ToDouble(d.Rows[0][5].ToString());
            t.TickStt = d.Rows[0][6].ToString();
            t.EvID = Convert.ToInt32(d.Rows[0][7].ToString());
            t.MaxOrder = Convert.ToInt32(d.Rows[0][8].ToString());
            return t;
        }
        public List<Event> LoadEventByCreatedUserID(int uid)
        {
            connectdata a = new connectdata();
            DataTable d = a.connect("SELECT * FROM Event WHERE Event.UserID='" + uid.ToString() + "'");
            List<Event> elist = new List<Event>();
            for (int i = 0; i <= d.Rows.Count - 1; i++)
            {
                Event e = new Event();
                e.EvID = Convert.ToInt32(d.Rows[i][0].ToString());
                e.evtitle = d.Rows[i][1].ToString();
                e.venuename = d.Rows[i][2].ToString();
                e.addr = d.Rows[i][3].ToString();
                e.b = Convert.ToDateTime(d.Rows[i][4].ToString());
                e.e = Convert.ToDateTime(d.Rows[i][5].ToString());
                e.uid = Convert.ToInt32(d.Rows[i][6].ToString());
                e.lid = Convert.ToInt32(d.Rows[i][7].ToString());
                e.evdes = d.Rows[i][8].ToString();
                e.org = d.Rows[i][9].ToString();
                e.location.city = d.Rows[i][10].ToString();
                e.location.province = d.Rows[i][11].ToString();
                e.location.country = d.Rows[i][12].ToString();
                elist.Add(e);

            }
            return elist;
        }
        public List<Event> LoadEventByOrderUserName(string username)
        {
            connectdata a = new connectdata();
            DataTable d = a.connect("SELECT TID FROM EventDb.dbo.OrderTicket WHERE Username='" + username + "'");
            List<Event> elist = new List<Event>();
            for (int i = 0; i <= d.Rows.Count - 1; i++)
            {
                Event e = LoadEventByID(LoadTicketByID(Convert.ToInt16(d.Rows[i][0].ToString())).EvID);
                elist.Add(e);
            }
            return elist;
        }
        //Tested
        public Event LoadEventByID(int EvID)
        {
            connectdata a = new connectdata();
            DataTable d = a.connect("SELECT * FROM Event WHERE Event.EvID='" + EvID.ToString() + "'");
            Event e = new Event();
            e.EvID = Convert.ToInt32(d.Rows[0][0].ToString());
            e.evtitle = d.Rows[0][1].ToString();
            e.venuename = d.Rows[0][2].ToString();
            e.addr = d.Rows[0][3].ToString();
            e.b = Convert.ToDateTime(d.Rows[0][4].ToString());
            e.e = Convert.ToDateTime(d.Rows[0][5].ToString());
            e.uid = Convert.ToInt32(d.Rows[0][6].ToString());
            e.lid = Convert.ToInt32(d.Rows[0][7].ToString());
            e.evdes = d.Rows[0][8].ToString();
            e.org = d.Rows[0][9].ToString();
            e.location.city = d.Rows[0][10].ToString();
            e.location.province = d.Rows[0][11].ToString();
            e.location.country = d.Rows[0][12].ToString();
            return e;
        }

    }
}
